﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class AccountManagerCtrl
    {
        #region 获取计划收入款项信息

        /// <summary>
        /// 获取指定日期的记账数据
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        internal static DataTable GetAccountManagerData(string startDate, string endDate, string accountType, string currentUser)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT * FROM fin_AccountManager WHERE [Date]>='{0}' AND [Date]<='{1}'", startDate, endDate);
            if (accountType != "全部")
                Sql = Sql + "AND [AccountType]='" + accountType + "'";
            if (currentUser !="")
                Sql = Sql + "AND [Transactor]='" + currentUser + "'";
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获得期初余额和时间段期末余额
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="accountType"></param>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        internal static void GetAccountManagerPeriod(string startDate, string endDate, string accountType, string currentUser,out decimal beginPeroid,out decimal endPeroid)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT SUM(Income-Expenditure) as BeginPeriod FROM fin_AccountManager WHERE [Date]<'{0}' AND [Date]>='2013-01-01'", startDate);
            if (accountType != "全部")
                Sql = Sql + "AND [AccountType]='" + accountType + "'";
            //if (currentUser != "")
            //    Sql = Sql + "AND [Transactor]='" + currentUser + "'";
            dbCommand = db.GetSqlStringCommand(Sql);

            object obj1 = db.ExecuteScalar(dbCommand);
            if (obj1 != DBNull.Value)
                beginPeroid = decimal.Parse(obj1.ToString());
            else
                beginPeroid = 0;
            Sql = string.Format(@"SELECT SUM(Income-Expenditure) as EndPeriod FROM fin_AccountManager WHERE [Date]<='{0}' AND [Date]>='2013-01-01'", endDate);
            if (accountType != "全部")
                Sql = Sql + "AND [AccountType]='" + accountType + "'";
            //if (currentUser != "")
            //    Sql = Sql + "AND [Transactor]='" + currentUser + "'";
            dbCommand = db.GetSqlStringCommand(Sql);

            object obj2 = db.ExecuteScalar(dbCommand);
            if (obj2 != DBNull.Value)
                endPeroid = decimal.Parse(obj2.ToString());
            else
                endPeroid = 0;
        }
        #endregion

        #region 计划收入款项信息的增、删、改

        internal static string CreateAccountManagerData(AccountManagerEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO [fin_AccountManager]([Date], [Summary], [Income], [Expenditure], [Remark],[ItemType],[ItemID],[Voucher],[AccountType],[PaymentMethod],[Transactor],[AuditState])
                                         VALUES('{0}','{1}',{2},{3},'{4}',{5},'{6}','{7}','{8}','{9}','{10}',{11});Select SCOPE_IDENTITY();",
                                        entity.Date,
                                        entity.Summary,
                                        entity.Income,
                                        entity.Expenditure,
                                        entity.Remark,
                                        entity.ItemType,
                                        entity.ItemID,
                                        entity.Voucher,
                                        entity.AccountType,
                                        entity.PaymentMethod,
                                        entity.Transactor,
                                        entity.AuditState);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteScalar(dbCommand).ToString();
        }

        /// <summary>
        /// 更新内容
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int UpdateAccountManagerData(AccountManagerEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [fin_AccountManager]
                                         SET [AuditState]={0},[Date]='{1}', [Summary]='{2}', [Income]={3}, [Expenditure]={4}, [Remark]='{5}'
                                         WHERE AccountManagerID={6}",
                                        entity.AuditState,
                                        entity.Date,
                                        entity.Summary,
                                        entity.Income,
                                        entity.Expenditure,
                                        entity.Remark,
                                        entity.AccountManagerID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新记账管理凭证信息
        /// </summary>
        /// <param name="itemType">记账类型</param>
        /// <param name="itemId">记账号</param>
        /// <param name="voucher">凭证号</param>
        /// <returns></returns>
        internal static int UpdateVoucherData(string itemType,string itemId,string voucher)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [fin_AccountManager] SET [Voucher]='{0}' WHERE ItemType={1} AND ItemID={2}", voucher,itemType,itemId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新记账管理凭证信息
        /// </summary>
        /// <param name="accountManagerID">记账ID</param>
       /// <param name="voucher">凭证号</param>
        /// <returns></returns>
        internal static int UpdateVoucherDataByID(string accountManagerID, string voucher)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [fin_AccountManager] SET [Voucher]='{0}' WHERE AccountManagerID={1}", voucher, accountManagerID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="incomeId"></param>
        /// <returns></returns>
        internal static int DeleteAccountManagerData(string accountManagerId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM [fin_AccountManager] WHERE AccountManagerID={0}", accountManagerId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

    }
}
